﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using UniRlv.COL;

namespace UniRlv.Utility
{
    public static class DALOpr
    {

        #region 通用添加/更新操作

        public static bool ExecuteNonQuery(string sql, ref string errDesc)
        {
            Console.WriteLine("ExecuteNonQuery: " + sql);
            return SqlHelper.ExecuteNonQuery(sql,  ref errDesc) >0;
        }

        public static bool ExecuteNonQueryTran(string[] sqls, ref string errDesc)
        {
            return SqlHelper.ExecuteSqlTran(sqls,  ref errDesc);
        }

        public static bool Insert<T>(T t, ref string errDesc) where T : class, new()
        {
            string sql = EntityHelper.GetInsertSql(t);
            Console.WriteLine("Insert: " + sql);
            return SqlHelper.ExecuteNonQuery(sql,  ref errDesc)>0;
        }

        public static bool Update(Dictionary<string, object> dataDic, string whereString, string tbName, ref string errDesc)
        {
            string sql = EntityHelper.GetUpdateSql(dataDic, whereString, tbName);
            return SqlHelper.ExecuteNonQuery(sql,  ref errDesc)>0;
        }

        #endregion


        #region 通用查询操作

        public static object RawGetScalar(string sql, ref string errDesc)
        {
            return SqlHelper.ExecuteScalar(sql,  ref errDesc);
        }

        public static string RawQuery(string sql)
        {
            Console.WriteLine("RawQuery: " + sql);

            List<Dictionary<string, object>> li = new List<Dictionary<string, object>>();
            Dictionary<string, object> res = new Dictionary<string, object>();
            bool bRes = false; ;
            string errDesc = string.Empty;


            DataTable dt = SqlHelper.ExecuteTable(sql, ref errDesc);

            if (dt != null)
            {
                Dictionary<string, object> dic;
                foreach (DataRow dr in dt.Rows)
                {
                    dic = new Dictionary<string, object>();
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        dic.Add(dt.Columns[i].ColumnName, dr[i].ToString());
                    }

                    li.Add(dic);
                }
                bRes = true;
            }



            res.Add("msg", errDesc);
            res.Add("res", bRes);
            res.Add("obj", JsonHelper.ToJson<List<Dictionary<string, object>>>(li));

            return JsonHelper.ToJson<Dictionary<string, object>>(res);
        }

        public static List<T> Query<T>(string whereString, ref string msg) where T : class, new()
        {
            string sql = EntityHelper.GetQuerySql<T>();
            sql += " where " + whereString;
            string res = RawQuery(sql);

            ResultEntity re = EntityHelper.GetResultEntityFromResult(res);
            Console.WriteLine("Query: " + sql);
            if (re.res)
            {
                return EntityHelper.GetObjectsFromResultEntity<T>(re);
            }
            else
            {
                msg = "查询任务出错：" + re.msg;
                return null;
            }

        }

        public static List<T> QueryAll<T>(ref string msg) where T : class, new()
        {
            return Query<T>("1=1", ref msg);
        }

        public static int QueryWholeQuantity<T>(ref string msg) where T : class, new()
        {
            return QueryLimitQuantity<T>("1=1", ref msg);
        }

        public static int QueryLimitQuantity<T>(string whereString, ref string msg) where T : class, new()
        {
            string tbName = typeof(T).Name;
            string sql = string.Format("select count(*) from {0} where {1}", tbName, whereString);
            return Convert.ToInt32(RawGetScalar(sql, ref msg));
        }

        public static List<T> QueryPage<T>(int page, int pageSize, ref string msg) where T : class, new()
        {
            return QueryPage<T>(page, pageSize, "1=1", ref msg);
        }

        public static List<T> QueryPage<T>(int page, int pageSize, string whereString, ref string msg) where T : class, new()
        {
            string tbName = typeof(T).Name;
            string sql = string.Format("SELECT w2.n, w1.* FROM {0} w1, (SELECT TOP {1} row_number() OVER (ORDER BY ID DESC) n, ID FROM {2} where {4}) w2 WHERE w1.ID = w2.ID AND w2.n > {3} ORDER BY w2.n ASC ", tbName, (page) * pageSize, tbName, (page - 1) * pageSize, whereString);

            string res = RawQuery(sql);
            ResultEntity re = EntityHelper.GetResultEntityFromResult(res);

            if (re.res)
            {
                return EntityHelper.GetObjectsFromResultEntity<T>(re);
            }
            else
            {
                msg = "查询任务出错：" + re.msg;
                return null;
            }
        }


        public static string RawQuerySingle(string sql, ref string msg)
        {
            Dictionary<string, object> res = new Dictionary<string, object>();
            Dictionary<string, object> obj = new Dictionary<string, object>();
            bool bRes = false;
            string errDesc = string.Empty;

            DataTable dt = SqlHelper.ExecuteTable(sql, ref errDesc);

            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    obj.Add(dt.Columns[i].ColumnName, dt.Rows[0][i].ToString());
                }

                bRes = true;
            }
            else
            {
                errDesc = "无法查到记录";
            }



            res.Add("msg", errDesc);
            res.Add("res", bRes);
            res.Add("obj", JsonHelper.ToJson<Dictionary<string, object>>(obj));

            return JsonHelper.ToJson<Dictionary<string, object>>(res);
        }

        public static T QueryByID<T>(string id, ref string msg) where T : class, new()
        {
            string sql = EntityHelper.GetQuerySql<T>();
            sql += " where id = " + id;
            return QuerySingle<T>(sql, ref msg);
        }

        public static T QuerySingle<T>(string sql, ref string msg) where T : class, new()
        {
            Console.WriteLine("QuerySingle: " + sql);
            string res = RawQuerySingle(sql, ref msg);
            ResultEntity re = EntityHelper.GetResultEntityFromResult(res);
            Console.WriteLine("QuerySingle: " + sql);
            if (re.res)
            {
                return EntityHelper.GetObjectFromResultEntity<T>(re);
            }
            else
            {
                msg = "查询任务出错：" + re.msg;
                return null;
            }

        }

        public static T QuerySingle<T>(ref string msg) where T : class, new()
        {
            string sql = EntityHelper.GetQuerySql<T>();
            string res = RawQuerySingle(sql, ref msg);

            Console.WriteLine("QuerySingle: " + sql);
            ResultEntity re = EntityHelper.GetResultEntityFromResult(res);

            if (re.res)
            {
                return EntityHelper.GetObjectFromResultEntity<T>(re);
            }
            else
            {
                msg = "查询任务出错：" + re.msg;
                return null;
            }

        }

        #endregion

        public static bool IfTableExist(string tbName)
        {
            return SqlHelper.IfTableExist(tbName);
        }

        public static bool IfColumnExist(string tbName, string col)
        {
            return SqlHelper.IfColumnExist(tbName, col);
        }

    }
}
